﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CallCenter.Data.BizUtilities
{
   public class MenuGroupPermissionUtility :BaseUtility
    {
        public MenuGroupPermissionUtility(DataManager manager)
            : base(manager)
        { }


        public int Insert(int nMenuID, int nGroupID)
        {

            int nRtn = -1;

           string sql  = "INSERT INTO MenuGroupPermissions " +
                "(MenuID, GroupID) " +
                "VALUES(@MenuID, @GroupID )" +
                "SELECT @pResult =@@IDENTITY";


            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@MenuID", SqlDbType.Int, 10, nMenuID);
                this.Factory.AddParameter(cmd, "@GroupID", SqlDbType.Int, 100, nGroupID);


                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn;

        }

   
        public void Update(int nID, int nMenuID, int nGroupID)
        {

            string sql = "UPDATE MenuGroupPermissions " +
                    "SET MenuID = @MenuID, " +
                    "GroupID = @GroupID " +
                    "WHERE ID = @ID";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@MenuID", SqlDbType.Int, 10, nMenuID);
                this.Factory.AddParameter(cmd, "@GroupID", SqlDbType.Int, 100, nGroupID);
              
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, nID);
                this.Factory.ExecuteNonQuery(cmd);

            }
        }
       
          

        public void Delete(int nID)
        {
            string sql  = "DELETE FROM MenuGroupPermissions " +
                "WHERE ID = @ID ";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, nID);

                this.Factory.ExecuteNonQuery(cmd);
            }
           
         
        }

        public DataTable GetAll()
        {
            string sql = "SELECT * FROM MenuGroupPermissions";



         return this.Factory.ExecuteTable(sql);
        }


        public DataRow GetRecordForID(int nID)
        {
            string sql = "SELECT * FROM MenuGroupPermissions WHERE ID=@ID";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, nID);

                this.Factory.ExecuteNonQuery(cmd);


                SqlDataAdapter da = new SqlDataAdapter(cmd);

                DataSet ds = new DataSet();
                da.Fill(ds, "MenuGroupPermissions");

                if (ds.Tables["MenuGroupPermissions"].Rows.Count > 0)
                    return ds.Tables["MenuGroupPermissions"].Rows[0];
                else
                    return null;
            }
        }

        public void DeleteMenuAll(int nMenuID)
        {
            string sql  = "DELETE FROM MenuGroupPermissions " +
                "WHERE MenuID = @MenuID ";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@MenuID", SqlDbType.Int, 10, nMenuID);

                this.Factory.ExecuteNonQuery(cmd);
            }
        }

        public void DeleteGroupAll(int nGroupID)
        {
           string sql =  "DELETE FROM MenuGroupPermissions " +
                "WHERE GroupID = @GroupID ";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@GroupID", SqlDbType.Int, 10, nGroupID);

                this.Factory.ExecuteNonQuery(cmd);
            }

  
        }

        public DataTable GetMenuItemsInGroup(int nGroupID)
        {
            string sql = "SELECT * FROM MenuGroupPermissions WHERE GroupID=" + nGroupID;
            return this.Factory.ExecuteTable(sql);
           
        }

        //public DataTable GetMenuItemsInGroupWithGroupNames(int nGroupID)
        //{
        //    SqlCommand cmdSelect = new SqlCommand("SELECT Persons.preferredName, MenuGroupPermissions.* " +
        //            "FROM Persons INNER JOIN MenuGroupPermissions ON Persons.ID = MenuGroupPermissions.PersonID " +
        //            "WHERE MenuGroupPermissions.GroupID = " + nGroupID, m_Connection);

        //    cmdSelect.CommandType = CommandType.Text;

        //    SqlDataAdapter da = new SqlDataAdapter(cmdSelect);

        //    DataSet ds = new DataSet();
        //    da.Fill(ds, "MenuGroupPermissions");

        //    return ds.Tables["MenuGroupPermissions"];
        //}

        public DataTable GetGroupsMenuItemIsIn(int nMenuID)
        {
            string sql = "SELECT * FROM MenuGroupPermissions WHERE MenuID=" + nMenuID;

            return this.Factory.ExecuteTable(sql);
       
        }

    }
}
